The Influence of Russia/Ukraine Conflict on US Economy

INFO 526 - Fall 2024 - Project - Stock Sleuths

Project description
Author
Affiliation

Stock Sleuths

School of Information, University of Arizona

Introduction

This analysis examines how the Russia-Ukraine conflict has impacted the U.S. economy by exploring key economic indicators, including the S&P 500 index, inflation rates, and consumer sentiment. We will clean, merge, and visualize these datasets to uncover trends and correlations.

Data Sources

The datasets include:

1. S&P 500 Index Data: Daily historical values for the S&P 500, a major U.S. stock market index, from 2014 to present.

2. Inflation Rate Data: Monthly U.S. inflation rates, which reflect economic inflation trends since 1978.

3. Consumer Sentiment Data: Monthly consumer sentiment data reflecting consumer confidence in the economy, from 1952 to present.

Setup

Data Cleaning

Data set 1: S&P 500 Index Data

The S&P 500 data set contains daily historical values for the S&P 500, a key stock market index that tracks the performance of 500 large U.S. companies.

Load and Preview Raw Data

# Load the S&P 500 data
sp500_data <- read_csv(here("data", "sp500_index.csv"))
glimpse(sp500_data)  # Preview raw data structure
Rows: 2,517
Columns: 2
$ Date     <date> 2014-10-13, 2014-10-14, 2014-10-15, 2014-10-16, 2014-10-17, …
$ `S&P500` <dbl> 1874.74, 1877.70, 1862.49, 1862.76, 1886.76, 1904.01, 1941.28…
datatable(sp500_data)

Cleaning and Transforming Data

# Clean and transform S&P 500 data
sp500_data <- sp500_data |>
  rename(date = Date, sp500_index = `S&P500`) |>
  mutate(date = as.Date(date, format = "%m-%d-%Y")) |>
  clean_names()

# Preview cleaned data
glimpse(sp500_data)
Rows: 2,517
Columns: 2
$ date        <date> 2014-10-13, 2014-10-14, 2014-10-15, 2014-10-16, 2014-10-1…
$ sp500_index <dbl> 1874.74, 1877.70, 1862.49, 1862.76, 1886.76, 1904.01, 1941…
summary(sp500_data)
      date             sp500_index  
 Min.   :2014-10-13   Min.   :1829  
 1st Qu.:2017-04-12   1st Qu.:2368  
 Median :2019-10-11   Median :2940  
 Mean   :2019-10-12   Mean   :3270  
 3rd Qu.:2022-04-11   3rd Qu.:4151  
 Max.   :2024-10-11   Max.   :5815  
datatable(sp500_data)

Insight: After cleaning, we have a dataset with date and sp500_index columns, making it easy to track stock market performance over time. The date formatting and column renaming help standardize the dataset for further analysis.

Data set 2: Inflation Rate Data

The inflation data set contains monthly U.S. inflation rates, which are essential for understanding changes in purchasing power and the cost of living.

Load and Preview Raw Data

# Load Inflation data
inflation_data <- read_csv(here("data", "MICH.csv"))
glimpse(inflation_data)  # Preview raw data structure
Rows: 560
Columns: 2
$ DATE <date> 1978-01-01, 1978-02-01, 1978-03-01, 1978-04-01, 1978-05-01, 1978…
$ MICH <dbl> 5.2, 6.4, 6.3, 6.7, 6.9, 6.5, 6.6, 8.7, 6.9, 7.4, 7.5, 7.3, 7.8, …
datatable(inflation_data)

Cleaning and Transforming Data

# Clean and transform inflation data
inflation_data <- inflation_data |>
  rename(date = DATE, inflation_rate = MICH) |>
  mutate(date = as.Date(date, format = "%m-%d-%Y")) |>
  clean_names()

# Preview cleaned data
glimpse(inflation_data)
Rows: 560
Columns: 2
$ date           <date> 1978-01-01, 1978-02-01, 1978-03-01, 1978-04-01, 1978-0…
$ inflation_rate <dbl> 5.2, 6.4, 6.3, 6.7, 6.9, 6.5, 6.6, 8.7, 6.9, 7.4, 7.5, …
datatable(inflation_data)

Insight: The cleaned inflation data provides a monthly view of inflation rates in the U.S., which we can later correlate with other indicators to see how inflation has responded to global events like the Russia-Ukraine conflict.

Dataset 3: Consumer Sentiment Data

The consumer sentiment data set captures the confidence levels of U.S. consumers, reflecting their optimism or pessimism about economic conditions.

Load and Preview Raw Data

# Load Consumer Sentiment data
sentiment_data <- read_csv(here("data", "UMCSENT.csv"))
glimpse(sentiment_data)  # Preview raw data structure
Rows: 862
Columns: 2
$ DATE    <date> 1952-11-01, 1952-12-01, 1953-01-01, 1953-02-01, 1953-03-01, 1…
$ UMCSENT <chr> "86.2", ".", ".", "90.7", ".", ".", ".", ".", ".", "80.8", "."…
datatable(sentiment_data)

Cleaning and Transforming Data

# Clean and transform consumer sentiment data
sentiment_data <- sentiment_data |>
  rename(date = DATE, consumer_sentiment = UMCSENT) |>
  mutate(date = as.Date(date, format = "%m-%d-%Y"),
         consumer_sentiment = na_if(consumer_sentiment, ".")) |>
  mutate(consumer_sentiment = as.numeric(consumer_sentiment)) |>
  clean_names()

# Preview cleaned data
glimpse(sentiment_data)
Rows: 862
Columns: 2
$ date               <date> 1952-11-01, 1952-12-01, 1953-01-01, 1953-02-01, 19…
$ consumer_sentiment <dbl> 86.2, NA, NA, 90.7, NA, NA, NA, NA, NA, 80.8, NA, N…
datatable(sentiment_data)

Insight: The consumer sentiment data now includes a clean, numeric consumer_sentiment column, allowing us to track consumer confidence over time. This indicator often correlates with spending and investment behavior, making it a valuable measure for economic analysis.

Data set 4: Oil Price Data

Oil price data is a critical indicator of economic dynamics, especially during geopolitical conflicts. This section loads, cleans, and analyzes oil data from multiple sheets in the Excel file.

Load and Inspect Oil Price Data

# Define file path for the oil price data set
file_path <- here("data", "pswrgvwall.xls")

# List all sheet names
sheet_names <- excel_sheets(file_path)

# Exclude "Contents" sheet and merge the remaining data sheets
oil_data <- map_dfr(sheet_names[!sheet_names %in% "Contents"], 
                    ~ read_excel(file_path, sheet = .x, skip = 2))

# Inspect the structure of the merged data
names(oil_data)
  [1] "Date"                                                                                                      
  [2] "Weekly U.S. Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                             
  [3] "Weekly East Coast Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
  [4] "Weekly New England (PADD 1A) Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"            
  [5] "Weekly Central Atlantic (PADD 1B) Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"       
  [6] "Weekly Lower Atlantic (PADD 1C) Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"         
  [7] "Weekly Midwest Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                          
  [8] "Weekly Gulf Coast Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
  [9] "Weekly Rocky Mountain Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                   
 [10] "Weekly West Coast Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
 [11] "Weekly Colorado Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                         
 [12] "Weekly Florida Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                          
 [13] "Weekly New York Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                         
 [14] "Weekly Minnesota Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                        
 [15] "Weekly Ohio Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                             
 [16] "Weekly Texas Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                            
 [17] "Weekly Washington Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
 [18] "Weekly Cleveland, OH Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                    
 [19] "Weekly Denver, CO Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
 [20] "Weekly Miami, FL Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                        
 [21] "Weekly Seattle, WA Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
 [22] "Weekly U.S. Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                             
 [23] "Weekly East Coast Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
 [24] "Weekly New England (PADD 1A) Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"            
 [25] "Weekly Central Atlantic (PADD 1B) Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"       
 [26] "Weekly Lower Atlantic (PADD 1C) Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"         
 [27] "Weekly Midwest Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                          
 [28] "Weekly Gulf Coast Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
 [29] "Weekly Rocky Mountain Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                   
 [30] "Weekly West Coast Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
 [31] "Weekly California Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
 [32] "Weekly Colorado Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                         
 [33] "Weekly Massachusetts Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                    
 [34] "Weekly Texas Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                            
 [35] "Weekly New York Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                         
 [36] "Weekly Houston, TX Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
 [37] "Weekly New York Harbor Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                  
 [38] "Weekly Boston, MA Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
 [39] "Weekly Chicago, IL Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
 [40] "Weekly Denver, CO Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
 [41] "Weekly Los Angeles, CA Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                  
 [42] "Weekly San Francisco, CA Regular Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                
 [43] "Weekly U.S. Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                         
 [44] "Weekly East Coast Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
 [45] "Weekly New England (PADD 1A) Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"        
 [46] "Weekly Central Atlantic (PADD 1B) Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"   
 [47] "Weekly Lower Atlantic (PADD 1C) Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"     
 [48] "Weekly Midwest Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
 [49] "Weekly Gulf Coast Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
 [50] "Weekly Rocky Mountain Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"               
 [51] "Weekly West Coast Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
 [52] "Weekly California Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
 [53] "Weekly Colorado Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
 [54] "Weekly Florida Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
 [55] "Weekly Massachusetts Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
 [56] "Weekly Minnesota Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                    
 [57] "Weekly New York Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
 [58] "Weekly Ohio Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                         
 [59] "Weekly Texas Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                        
 [60] "Weekly Washington Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
 [61] "Weekly Boston, MA Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
 [62] "Weekly Chicago Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
 [63] "Weekly Cleveland, OH Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
 [64] "Weekly Denver Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                       
 [65] "Weekly Houston Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
 [66] "Weekly Los Angeles Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
 [67] "Weekly Miami, FL Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                    
 [68] "Weekly New York City Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
 [69] "Weekly San Francisco Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
 [70] "Weekly Seattle, WA Regular All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
 [71] "Weekly U.S. Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                            
 [72] "Weekly East Coast Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
 [73] "Weekly New England (PADD 1A) Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"           
 [74] "Weekly Central Atlantic (PADD 1B) Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"      
 [75] "Weekly Lower Atlantic (PADD 1C) Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"        
 [76] "Weekly Midwest Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                         
 [77] "Weekly Gulf Coast Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
 [78] "Weekly Rocky Mountain Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                  
 [79] "Weekly West Coast Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
 [80] "Weekly Colorado Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                        
 [81] "Weekly Florida Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                         
 [82] "Weekly Minnesota Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
 [83] "Weekly New York Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                        
 [84] "Weekly Ohio Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                            
 [85] "Weekly Texas Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                           
 [86] "Weekly Washington Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
 [87] "Weekly Cleveland, OH Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                   
 [88] "Weekly Denver, CO Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
 [89] "Weekly Miami, FL Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
 [90] "Weekly Seattle, WA Midgrade Conventional Retail Gasoline Prices  (Dollars per Gallon)"                     
 [91] "Weekly U.S. Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                            
 [92] "Weekly East Coast Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
 [93] "Weekly New England (PADD 1A) Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"           
 [94] "Weekly Central Atlantic (PADD 1B) Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"      
 [95] "Weekly Lower Atlantic (PADD 1C) Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"        
 [96] "Weekly Midwest Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                         
 [97] "Weekly Gulf Coast Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
 [98] "Weekly Rocky Mountain Midgrades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                 
 [99] "Weekly West Coast Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
[100] "Weekly California Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
[101] "Weekly Colorado Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                        
[102] "Weekly Massachusetts Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                   
[103] "Weekly Texas Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                           
[104] "Weekly New York Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                        
[105] "Weekly Denver, CO Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
[106] "Weekly Boston, MA Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
[107] "Weekly Chicago, IL Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                     
[108] "Weekly Houston, TX Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                     
[109] "Weekly Los Angeles, CA Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                 
[110] "Weekly New York Harbor Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                 
[111] "Weekly San Francisco, CA Midgrade Reformulated Retail Gasoline Prices  (Dollars per Gallon)"               
[112] "Weekly U.S. Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                        
[113] "Weekly East Coast Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[114] "Weekly New England (PADD 1A) Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"       
[115] "Weekly Central Atlantic (PADD 1B) Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"  
[116] "Weekly Lower Atlantic (PADD 1C) Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"    
[117] "Weekly Midwest Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
[118] "Weekly Gulf Coast Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[119] "Weekly Rocky Mountain Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"              
[120] "Weekly West Coast Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[121] "Weekly California Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[122] "Weekly Colorado Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                    
[123] "Weekly Florida Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
[124] "Weekly Massachusetts Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"               
[125] "Weekly Minnesota Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[126] "Weekly New York Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                    
[127] "Weekly Ohio Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                        
[128] "Weekly Texas Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                       
[129] "Weekly Washington Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[130] "Weekly Boston, MA Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[131] "Weekly Chicago Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
[132] "Weekly Cleveland, OH Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"               
[133] "Weekly Denver Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
[134] "Weekly Houston Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
[135] "Weekly Los Angeles Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                 
[136] "Weekly Miami, FL Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[137] "Weekly New York City Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"               
[138] "Weekly San Francisco Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"               
[139] "Weekly Seattle, WA Midgrade All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                 
[140] "Weekly U.S. Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                             
[141] "Weekly East Coast Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
[142] "Weekly New England (PADD 1A) Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"            
[143] "Weekly Central Atlantic (PADD 1B) Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"       
[144] "Weekly Lower Atlantic (PADD 1C) Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"         
[145] "Weekly Midwest Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                          
[146] "Weekly Gulf Coast Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
[147] "Weekly Rocky Mountain Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                   
[148] "Weekly West Coast Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
[149] "Weekly Colorado Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                         
[150] "Weekly Florida Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                          
[151] "Weekly Minnesota Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                        
[152] "Weekly New York Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                         
[153] "Weekly Ohio Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                             
[154] "Weekly Texas Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                            
[155] "Weekly Washington Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
[156] "Weekly Cleveland, OH Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                    
[157] "Weekly Denver, CO Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
[158] "Weekly Miami, FL Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                        
[159] "Weekly Seattle, WA Premium Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
[160] "Weekly U.S. Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                             
[161] "Weekly East Coast Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
[162] "Weekly New England (PADD 1A) Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"            
[163] "Weekly Central Atlantic (PADD 1B) Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"       
[164] "Weekly Lower Atlantic (PADD 1C) Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"         
[165] "Weekly Midwest Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                          
[166] "Weekly Gulf Coast Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
[167] "Weekly Rocky Mountain Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                   
[168] "Weekly West Coast Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
[169] "Weekly California Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
[170] "Weekly Colorado Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                         
[171] "Weekly Massachusetts Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                    
[172] "Weekly Texas Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                            
[173] "Weekly New York Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                         
[174] "Weekly Denver, CO Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
[175] "Weekly Boston, MA Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
[176] "Weekly Chicago, IL Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
[177] "Weekly Houston, TX Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
[178] "Weekly Los Angeles, CA Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                  
[179] "Weekly New York Harbor Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                  
[180] "Weekly San Francisco, CA Premium Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                
[181] "Weekly U.S. Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                         
[182] "Weekly East Coast Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[183] "Weekly New England (PADD 1A) Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"        
[184] "Weekly Central Atlantic (PADD 1B) Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"   
[185] "Weekly Lower Atlantic (PADD 1C) Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"     
[186] "Weekly Midwest Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
[187] "Weekly Gulf Coast Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[188] "Weekly Rocky Mountain Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"               
[189] "Weekly West Coast Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[190] "Weekly California Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[191] "Weekly Colorado Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
[192] "Weekly Florida Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
[193] "Weekly Massachusetts Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[194] "Weekly Minnesota Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                    
[195] "Weekly New York Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
[196] "Weekly Ohio Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                         
[197] "Weekly Texas Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                        
[198] "Weekly Washington Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[199] "Weekly Boston, MA Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[200] "Weekly Chicago Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
[201] "Weekly Cleveland, OH Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[202] "Weekly Denver Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                       
[203] "Weekly Houston Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
[204] "Weekly Los Angeles Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[205] "Weekly Miami, FL Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                    
[206] "Weekly New York City Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[207] "Weekly San Francisco Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[208] "Weekly Seattle, WA Premium All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[209] "Weekly U.S. All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                          
[210] "Weekly East Coast All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                    
[211] "Weekly New England (PADD 1A) All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"         
[212] "Weekly Central Atlantic (PADD 1B) All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"    
[213] "Weekly Lower Atlantic (PADD 1C) All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"      
[214] "Weekly Midwest All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
[215] "Weekly Gulf Coast All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                    
[216] "Weekly Rocky Mountain All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                
[217] "Weekly West Coast All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                    
[218] "Weekly Colorado All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
[219] "Weekly Florida All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                       
[220] "Weekly Minnesota All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                     
[221] "Weekly New York All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                      
[222] "Weekly Ohio All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                          
[223] "Weekly Texas All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                         
[224] "Weekly Washington All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                    
[225] "Weekly Cleveland, OH All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                 
[226] "Weekly Denver, CO All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                    
[227] "Weekly Miami, FL All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                     
[228] "Weekly Seattle, WA All Grades Conventional Retail Gasoline Prices  (Dollars per Gallon)"                   
[229] "Weekly U.S. All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                          
[230] "Weekly East Coast All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                    
[231] "Weekly New England (PADD 1A) All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"         
[232] "Weekly Central Atlantic (PADD 1B) All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"    
[233] "Weekly Lower Atlantic (PADD 1C) All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"      
[234] "Weekly Midwest All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
[235] "Weekly Gulf Coast All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                    
[236] "Weekly Rocky Mountain Allgrades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                 
[237] "Weekly West Coast All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                    
[238] "Weekly California All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                    
[239] "Weekly Colorado Allgrades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                       
[240] "Weekly Massachusetts All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                 
[241] "Weekly Texas All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                         
[242] "Weekly New York All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                      
[243] "Weekly Denver, CO Allgrades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                     
[244] "Weekly Boston, MA All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                    
[245] "Weekly Chicago, IL All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                   
[246] "Weekly Houston, TX All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"                   
[247] "Weekly Los Angeles, CA All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"               
[248] "Weekly New York Harbor All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"               
[249] "Weekly San Francisco, CA All Grades Reformulated Retail Gasoline Prices  (Dollars per Gallon)"             
[250] "Weekly U.S. All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
[251] "Weekly East Coast All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[252] "Weekly New England (PADD 1A) All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"     
[253] "Weekly Central Atlantic (PADD 1B) All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"
[254] "Weekly Lower Atlantic (PADD 1C) All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"  
[255] "Weekly Midwest All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[256] "Weekly Gulf Coast All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[257] "Weekly Rocky Mountain All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"            
[258] "Weekly West Coast All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[259] "Weekly California All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[260] "Weekly Colorado All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[261] "Weekly Florida All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[262] "Weekly Massachusetts All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"             
[263] "Weekly Minnesota All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                 
[264] "Weekly New York All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                  
[265] "Weekly Ohio All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                      
[266] "Weekly Texas All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                     
[267] "Weekly Washington All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[268] "Weekly Boston, MA All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                
[269] "Weekly Chicago All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[270] "Weekly Cleveland, OH All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"             
[271] "Weekly Denver All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                    
[272] "Weekly Houston All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                   
[273] "Weekly Los Angeles All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"               
[274] "Weekly Miami, FL All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"                 
[275] "Weekly New York City All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"             
[276] "Weekly San Francisco All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"             
[277] "Weekly Seattle, WA All Grades All Formulations Retail Gasoline Prices  (Dollars per Gallon)"               

Cleaning and Transforming Data

# Rename relevant columns and clean the data
oil_data <- map_dfr(sheet_names[!sheet_names %in% "Contents"], 
                    ~ read_excel(file_path, sheet = .x, skip = 2))

# Select relevant columns
oil_data <- oil_data |>
  select(
    date = `Date`, 
    oil_price = `Weekly U.S. Regular Conventional Retail Gasoline Prices  (Dollars per Gallon)`
  )

# Rename columns for simplicity
oil_data <- oil_data |>
  clean_names() |>
  mutate(
    date = as.Date(date, format = "%Y-%m-%d"),
    oil_price = as.numeric(oil_price)
  ) |>
  filter(!is.na(oil_price))  # Remove rows with missing oil prices

# Preview the cleaned oil data
# glimpse(oil_data)
datatable(oil_data)
# Save cleaned oil data
write_csv(oil_data, here("data", "cleaned_oil_data.csv"))

Identifying Missing Values Across Data sets

To ensure data quality, we summarize missing values across all data cd sets. This helps identify any gaps before merging and analyzing the data.

# Missing values summary for each dataset
missing_summary <- list(
  S_P500 = sp500_data |> 
    summarize(across(everything(),
                     ~ sum(is.na(.))
                     )
              ),
  Inflation = inflation_data |> 
    summarize(across(everything(), 
                     ~ sum(is.na(.))
                     )
              ),
  Consumer_Sentiment = sentiment_data |> 
    summarize(across(everything(),
                     ~ sum(is.na(.))
                     )
              )
  ) |> 
  bind_rows(.id = "dataset")

missing_summary
# A tibble: 3 × 5
  dataset             date sp500_index inflation_rate consumer_sentiment
  <chr>              <int>       <int>          <int>              <int>
1 S_P500                 0           0             NA                 NA
2 Inflation              0          NA              0                 NA
3 Consumer_Sentiment     0          NA             NA                210
datatable(missing_summary)

Insight: By summarizing missing values, we can see if there are any gaps in the data. If significant gaps exist, we may need to address these before merging data sets to ensure our analysis remains accurate and representative.

Merging the Data sets

Now that each data set is cleaned, we merge them by the date column to create a comprehensive data set for analysis.

## Merging the Datasets
merged_data <- sentiment_data |>
  left_join(inflation_data, by = "date") |>
  left_join(sp500_data, by = "date")

# Display the column names and structure of the merged dataset
glimpse(merged_data)
Rows: 862
Columns: 4
$ date               <date> 1952-11-01, 1952-12-01, 1953-01-01, 1953-02-01, 19…
$ consumer_sentiment <dbl> 86.2, NA, NA, 90.7, NA, NA, NA, NA, NA, 80.8, NA, N…
$ inflation_rate     <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ sp500_index        <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
datatable(merged_data)
# Display a complete summary of the merged dataset
summary(merged_data)
      date            consumer_sentiment inflation_rate    sp500_index  
 Min.   :1952-11-01   Min.   : 50.00     Min.   : 0.400   Min.   :1914  
 1st Qu.:1970-10-08   1st Qu.: 75.60     1st Qu.: 2.800   1st Qu.:2413  
 Median :1988-09-16   Median : 89.00     Median : 3.000   Median :2954  
 Mean   :1988-09-15   Mean   : 85.33     Mean   : 3.586   Mean   :3300  
 3rd Qu.:2006-08-24   3rd Qu.: 94.80     3rd Qu.: 3.500   3rd Qu.:4185  
 Max.   :2024-08-01   Max.   :112.00     Max.   :10.400   Max.   :5475  
                      NA's   :210        NA's   :302      NA's   :783   
# Use skimr for an enhanced summary
skim(merged_data)
Data summary
Name merged_data
Number of rows 862
Number of columns 4
_______________________
Column type frequency:
Date 1
numeric 3
________________________
Group variables None

Variable type: Date

skim_variable n_missing complete_rate min max median n_unique
date 0 1 1952-11-01 2024-08-01 1988-09-16 862

Variable type: numeric

skim_variable n_missing complete_rate mean sd p0 p25 p50 p75 p100 hist
consumer_sentiment 210 0.76 85.33 12.94 50.00 75.60 89.00 94.80 112.00 ▁▃▅▇▂
inflation_rate 302 0.65 3.59 1.62 0.40 2.80 3.00 3.50 10.40 ▁▇▁▁▁
sp500_index 783 0.09 3299.66 1027.77 1913.85 2413.01 2953.56 4184.96 5475.09 ▇▆▃▆▂

Insight: The merged data set combines the S&P 500 index, inflation rates, and consumer sentiment data, all aligned by date. This allows us to analyze the interplay between stock market trends, inflation, and consumer sentiment over time, providing a holistic view of economic dynamics.

Saving the Merged Data

Save the merged data set as a CSV file in the data folder for future analysis.

# Save the merged dataset to the `data` folder
write_csv(merged_data, here("data", "merged_economic_indicators.csv"))

Exploratory Data Visualization

We visualize each indicator over time to observe trends and identify any interesting patterns or potential correlations.

Visualization of Oil Prices

Descriptive analysis for oil prices

# Calculate basic statistics for oil prices
oil_stats <- oil_data |>
  summarize(
    mean_price = mean(oil_price, na.rm = TRUE),
    median_price = median(oil_price, na.rm = TRUE),
    sd_price = sd(oil_price, na.rm = TRUE),
    min_price = min(oil_price, na.rm = TRUE),
    max_price = max(oil_price, na.rm = TRUE)
  )

datatable(oil_stats)

Data Visualization

Oil Prices vs S&P 500 Stock Analysis:

oil <- read_csv(here("data","cleaned_oil_data.csv"))
oil$date <- as.Date(oil$date, format = "%Y-%m-%d")

sp500_index <- read_csv(here("data","sp500_index.csv"))
sp500_index$Date <- as.Date(sp500_index$Date, format = "%Y-%m-%d")

sp500_index <- sp500_index |>
  rename(date = Date)

oil_2022 <- subset(oil, format(date, "%Y") == "2022")
sp500_2022 <- subset(sp500_index, format(date, "%Y") == "2022")

merged_data <- merge(oil_2022, sp500_2022, by = "date")

plot_1 <- ggplot(data = merged_data, aes(x = oil_price, y = `S&P500`)) +
  geom_point(color = "black", size = 3) +
  geom_smooth(method = "lm", color = "red", se = FALSE) +
  labs(
    title = "Oil Prices vs S&P 500 Prices (2022)",
    x = "Oil Prices",
    y = "S&P 500 Prices"
  ) +
  scale_x_continuous(labels = dollar) +
  scale_y_continuous(labels = dollar) +
  theme_minimal()
# Saving the plot
# Saving the plot in high resolution
ggsave(
  filename = here("images", "oil_vs_stocks_scatterplot.png"),
  plot = plot_1,
  width = 10,          # Adjust width as per your need
  height = 8,          # Adjust height as per your need
  dpi = 300            # Set DPI to 300 for high-quality output
)
Observations:

The scatter plot shows the relationship between oil prices and S&P 500 prices for the year 2022. The trend line in the graph suggests a negative correlation, meaning that as oil prices increase, S&P 500 prices tend to decrease. However, the points on the graph are quite spread out, which means the relationship is not very strong or consistent. This could indicate that while oil prices might have some impact on the S&P 500, there are likely other factors at play, such as changes in the economy, interest rates, or global events. Overall, the graph shows that there might be a slight connection between oil prices and stock performance, but it’s not strong enough to say one directly causes the other.